﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Provausio.Common.Data
{
    public sealed class SqlHelper
    {
        public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType,string commandText, IEnumerable<SqlParameter> parameters = null)
        {
            var conn = new SqlConnection(connectionString);
            var comm = new SqlCommand(commandText, conn);
            comm.CommandType = commandType;

            if(parameters != null) comm.Parameters.AddRange(parameters.ToArray());
            try
            {
                conn.Open();
                return comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception)
            {
                conn.Close();
                conn.Dispose();
                throw;
            }
        }

        /// <summary>
        /// Executes a SQL command and does not return a result set.
        /// </summary>
        /// <param name="connectionString">The database connection string</param>
        /// <param name="commandType">Specify the command type</param>
        /// <param name="commandText">THe SQL command or stored procedure to be run</param>
        /// <param name="parameters">Optional list of parameters</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, IEnumerable<SqlParameter> parameters = null)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                using (var comm = new SqlCommand(commandText, conn) {CommandType = commandType})
                {
                    if (parameters != null) comm.Parameters.AddRange(parameters.ToArray());
                    conn.Open();
                    return comm.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Expects to return a single value result of sql command
        /// </summary>
        /// <typeparam name="T">The expected return type</typeparam>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="commandType">Sql command type</param>
        /// <param name="commandText">Sql command or stored procedure name</param>
        /// <param name="parameters">Optional parameter list</param>
        /// <returns>Returns default value of return type if database value is null or error occurs. Be sure to specify if a value type is nullable or not. (int?)</returns>
        public static T ExecuteScalar<T>(string connectionString, CommandType commandType, string commandText, IEnumerable<SqlParameter> parameters = null)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                using (var comm = new SqlCommand(commandText, conn) {CommandType = commandType})
                {
                    if (parameters != null) comm.Parameters.Add(parameters);
                    conn.Open();
                    var result = comm.ExecuteScalar();
                    try
                    {
                        if (result != null && result != DBNull.Value)
                        {
                            return (T)result;
                        }
                        return default(T);
                    }
                    catch (Exception)
                    {
                        return default(T);
                    }
                }
            }
        }

        public static int QueryToCsv(string connectionString, CommandType commandType, string commandText, IEnumerable<SqlParameter> parameters = null)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Executes a batch of similar sql commands on the same connection. If any exceptions occur, the entire transaction will be rolled back.
        /// </summary>
        /// <typeparam name="T">Type of object that will processed for parameters</typeparam>
        /// <param name="connectionString">DB connection string</param>
        /// <param name="commandType">Command Type</param>
        /// <param name="commandText">Sql command</param>
        /// <param name="listItems">List of objects containing values to be set as parameters</param>
        /// <param name="setParameters">Delegate containing instructions on how to create the parameter list from the list of objects</param>
        /// <returns>Returns false only if all list items fail</returns>
        public static bool ExecuteBulkNonQuery<T>(string connectionString, CommandType commandType, string commandText, 
            IEnumerable<T> listItems, Func<T, SqlParameter[]> setParameters)
        {
            var fails = 0;
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();
                var trans = conn.BeginTransaction();
                using (var comm = new SqlCommand(commandText, conn))
                {
                    try
                    {
                        comm.CommandType = commandType;
                        comm.Transaction = trans;
                        foreach (var obj in listItems)
                        {
                            comm.Parameters.Clear();
                            comm.Parameters.AddRange(setParameters.Invoke(obj));
                            //TODO log the failure (if any)
                            fails += comm.ExecuteNonQuery();
                        }
                        if (fails != 0)
                        {
                            comm.Transaction.Commit();
                            return true;
                        }
                        return false;
                    }
                    catch (Exception)
                    {
                        //TODO logging
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }
    }
}
